package com.dhcc.DRGsHosp.application.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.dayatang.querychannel.QueryChannelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.DifficultGroupMonitorApplication;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
import com.dhcc.DRGsHosp.infra.util.StringUtils;
@Named
@Transactional
public class DifficultGroupMonitorApplicationImpl implements DifficultGroupMonitorApplication{

	@Autowired
	private JdbcTemplateObj jdbcTemplate;

	private EntityRepository repository;
    
	private QueryChannelService queryChannel;
	
    private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}
    private QueryChannelService getQueryChannelService(){
        if(queryChannel==null){
           queryChannel = InstanceFactory.getInstance(QueryChannelService.class,"queryChannel");
        }
      return queryChannel;
     }
	/**
	 * 
	 *  @Description：疑难病组组数
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.DifficultGroupMonitorApplication#getTotalCountDiffi(java.lang.String)
	 */
	@Override
	public Map<String, Object> getTotalCountDiffi(String defalutType,String roleName,String account,String hospCode) {
		String cfgCode = getCfgCode(account,hospCode);
		String sql="  select count(pd.drg_code) diffiamount from drgs.MR_PUZZLE_DRG pd where 1=1 ";
		sql+="  and (pd.del_type is null or pd.del_type='0') and par_moudle_code='"+cfgCode+"'\n";
		return jdbcTemplate.queryForMap(sql);
	}

	/**
	 * 
	 *  @Description：疑难病组病例数占比和覆盖率
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.DifficultGroupMonitorApplication#getDiffiAccount(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getDiffiAccount(String dateF, String dateT, String defalutType,String groupScheme,String roleName,String account,String hospCode) {
		String cfgCode = getCfgCode(account,hospCode);
		String sql="  select sum(case when pd.del_type='0' then 1 else 0 end) difficnt,count(ip.pmainindex) incnt,  \n";
		sql+="  count(distinct  pd.drg_code) difficount,  \n";
		sql+="  count(distinct p.pardrg_code) incount  \n";
		sql+="  from drgs.mr_ipinfo ip  \n";
		sql+="  left join  drgs.mr_baseinfo bs on bs.pmainindex=ip.pmainindex \n";
		sql+="  left join drgs.t_patientdrg p on ip.pmainindex=p.parid and p.ver_region='"+groupScheme+"' \n";
		sql+="  left join drgs.mr_puzzle_drg pd on p.pardrg_code=pd.drg_code  and par_moudle_code='"+cfgCode+"' \n";
		sql+="  where p.pardrg_code is not null  and instr('"+hospCode+"',bs.p00010001)>0 \n";
		sql+="  and to_char(ip.p00290000,'yyyy-mm-dd') between '"+dateF+"' and '"+dateT+"'  \n";
		sql+="  and (pd.del_type is null or pd.del_type='0') \n";
		return jdbcTemplate.queryForList(sql);
	}

	/**
	 * 
	 *  @Description：院内疑难病组按MDC分布
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.DifficultGroupMonitorApplication#getDiffiGroupMdc(java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getDiffiGroupMdc(String stdId, String yearCode,String groupScheme,String roleName,String account,String hospCode) {
		String cfgCode = getCfgCode(account,hospCode);
		List<String> years=new ArrayList<String>();
		if (yearCode.indexOf(",") > 0) {
		    years = Arrays.asList(yearCode.split(","));
        }else {
        	years.add(yearCode);
        }
        
		StringBuilder sql=new StringBuilder();
		sql.append("  select mdc.mdc_code,mdc.mdc_desc,  \n");
		sql.append("  count(distinct p.pardrg_code) drg_count,count(p.parid)in_cnt, \n");
		sql.append("  sum(case when ann.as_weight is not null then ann.as_weight else 0 end) all_weight, \n");
		sql.append("  round(avg(ann.as_weight),2) cmi \n");
		sql.append("  from drgs.mr_puzzle_drg pd left join drgs.t_patientdrg p on pd.drg_code=p.pardrg_code and p.ver_region='"+groupScheme+"' and pd.par_moudle_code='"+cfgCode+"' \n");
		sql.append("  left join drgs.mr_ipinfo info on p.parid=info.pmainindex \n");
		sql.append("  left join  drgs.mr_baseinfo bs on bs.pmainindex=info.pmainindex \n");
		sql.append("  left join drgs.t_annualstd ann on p.pardrg_code=ann.as_drg_code and ann.as_id='"+stdId+"' \n");
		sql.append("  left join drgs.t_mdcgroup mdc on p.mdc=mdc.mdc_code and p.ver_code=mdc.ver_code \n");
		sql.append("  where p.pardrg_code is not null and instr('"+hospCode+"',bs.p00010001)>0 \n");
		
		if(null!=years){
            String yearSql = " and (";
            for(int i = 0;i <years.size();i++){
                yearSql += " to_char(info.P00290000,'yyyy')='"+years.get(i)+"' or \n ";
            }
            yearSql = yearSql.substring(0, yearSql.lastIndexOf("or"));
            yearSql += " )";
            sql.append(yearSql);
        }		
		sql.append("  and (pd.del_type is null or pd.del_type='0')  \n");
		sql.append("  group by mdc.mdc_code,mdc.mdc_desc \n");
		return jdbcTemplate.queryForList(sql.toString());
	}

	/**
	 * 
	 *  @Description：院内疑难病组按MDC下DRG分布
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.DifficultGroupMonitorApplication#getDiffiGroupDrg(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getDiffiGroupDrg(String yearCode,String stdId, String mdcCode,String groupScheme,String roleName,String account,String hospCode) {
		String cfgCode = getCfgCode(account,hospCode);
		List<String> years=new ArrayList<String>();
		if (yearCode.indexOf(",") > 0) {
		    years = Arrays.asList(yearCode.split(","));
        }else {
        	years.add(yearCode);
        }
        
		StringBuilder sql=new StringBuilder();
		sql.append("  select b.mdc_code, b.weight_desc,b.mdc_desc,b.drg_code,b.drg_desc,b.drg_weight,b.in_cnt,b.all_weight,rownum incnt_desc \n");
		sql.append("  from( \n");
		sql.append("  select  a.mdc_code,rownum weight_desc,a.mdc_desc,a.drg_code,a.drg_desc,a.drg_weight,a.in_cnt,a.all_weight \n");
		sql.append("  from( \n");
		sql.append("  select p.mdc mdc_code,mdc.mdc_desc,drg.drg_code,drg.drg_desc,ann.as_weight drg_weight,count(p.parid)in_cnt,  \n");
		sql.append("  sum(case when ann.as_weight is not null then ann.as_weight else 0 end) all_weight  \n");
		sql.append("  from drgs.mr_puzzle_drg pd left join drgs.t_patientdrg p on pd.drg_code=p.pardrg_code and p.ver_region='"+groupScheme+"' and pd.par_moudle_code='"+cfgCode+"' \n");
		sql.append("  left join drgs.mr_ipinfo info on p.parid=info.pmainindex  \n");
		sql.append("  left join  drgs.mr_baseinfo bs on bs.pmainindex=info.pmainindex \n");
		sql.append("  left join drgs.t_annualstd ann on p.pardrg_code=ann.as_drg_code and ann.as_id='"+stdId+"'  \n");
		sql.append("  left join drgs.t_drgs drg on p.pardrg_code=drg.drg_code and p.ver_code=drg.ver_code  \n");
		sql.append("  left join drgs.t_mdcgroup mdc on p.mdc=mdc.mdc_code and mdc.ver_code = drg.ver_code   \n");
		sql.append("  where p.pardrg_code is not null and instr('"+hospCode+"',bs.p00010001)>0 \n");
		
		if(null!=years){
		    String yearSql = " and (";
            for(int i = 0;i <years.size();i++){
                yearSql += " to_char(info.P00290000,'yyyy')='"+years.get(i)+"' or \n ";
            }
            yearSql = yearSql.substring(0, yearSql.lastIndexOf("or"));
            yearSql += " )";
            sql.append(yearSql);
        }
		
		
		if(StringUtils.hasLength(mdcCode)){
			sql.append("  and p.mdc='"+mdcCode+"'  \n");	
		}
		sql.append("  and (pd.del_type is null or pd.del_type='0') \n");
		sql.append("  group by drg.drg_code,drg.drg_desc,ann.as_weight,p.mdc,mdc.mdc_desc  \n");
		sql.append("  order by all_weight desc nulls last )a \n");
		sql.append("  order by a.in_cnt desc)b order by b.mdc_code,b.drg_code \n");
		return jdbcTemplate.queryForList(sql.toString());
	}

	/**
	 * 
	 *  @Description：删除疑难病组
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.DifficultGroupMonitorApplication#delDiffiGroup(java.lang.String)
	 */
	@Override
	public void delDiffiGroup(String drgCode,String roleName,String account,String hospCode) {
		String cfgCode = getCfgCode(account,hospCode);
		String sql="  update drgs.mr_puzzle_drg pd set pd.del_type='1' where pd.drg_code='"+drgCode+"' and pd.par_moudle_code='"+cfgCode+"'";
		jdbcTemplate.update(sql);
	}

	/**
	 * 
	 *  @Description：获取疑难病组以外的病组 让用户筛选
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.DifficultGroupMonitorApplication#getExtraDiffiGroup(java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getExtraDiffiGroup(String stdId, String yearCode,String groupScheme,String roleName,String account,String hospCode) {
		String cfgCode = getCfgCode(account,hospCode);
		List<String> years=new ArrayList<String>();
		if (yearCode.indexOf(",") > 0) {
		    years = Arrays.asList(yearCode.split(","));
        }else {
        	years.add(yearCode);
        }
        
		StringBuilder sql=new StringBuilder();
		sql.append("  select  mdc.mdc_code mdcCode,mdc.mdc_desc mdcDesc,pardrg_code drgCode,drg.drg_desc drgDesc,casecnt, \n");
		sql.append("  casecntrow caseCntRange,as_weight weight,totalweight,totalweightrow  totalWeightRange from (  \n");
		sql.append("  select pardrg_code,ver_code,caseCnt,as_weight,totalWeight,totalWeightRow,rownum caseCntRow from ( \n");
		sql.append("  select pardrg_code,ver_code,caseCnt,as_weight,totalWeight,rownum totalWeightRow from \n");
		sql.append("  (select pardrg_code,ver_code,caseCnt,as_weight,totalWeight from ( \n");
		sql.append("  select p.pardrg_code,p.ver_code,count(p.parid) caseCnt,avg(ann.as_weight) as_weight,sum(ann.as_weight)  totalWeight \n");
		sql.append("  from drgs.t_patientdrg p left join drgs.mr_ipinfo mr on p.parid = mr.pmainindex and p.ver_region='"+groupScheme+"' \n");
		sql.append("  left join  drgs.mr_baseinfo bs on bs.pmainindex=mr.pmainindex  \n");
		sql.append("  left join drgs.t_annualstd ann on ann.as_drg_code = p.pardrg_code and ann.as_id = '"+stdId+"' \n");
		sql.append("  where p.pardrg_code is not null and instr('"+hospCode+"',bs.p00010001)>0 \n");
		if(null!=years){
            String yearSql = " and (";
            for(int i = 0;i <years.size();i++){
                yearSql += " to_char(mr.P00290000,'yyyy')='"+years.get(i)+"' or \n ";
            }
            yearSql = yearSql.substring(0, yearSql.lastIndexOf("or"));
            yearSql += " )";
            sql.append(yearSql);
        }
		
		sql.append("  and not exists (select pd.drg_code from drgs.mr_puzzle_drg pd where pd.drg_code = p.pardrg_code and pd.par_moudle_code='"+cfgCode+"' \n");
		sql.append("  and (pd.del_type='0' or pd.del_type is null)) \n");
		sql.append("  group by p.pardrg_code,p.ver_code) a \n");
		sql.append("  order by totalWeight desc nulls last) order by caseCnt desc)) a \n");
		sql.append("  left join drgs.t_drgs drg on drg.drg_code = a.pardrg_code and drg.ver_code = a.ver_code \n");
		sql.append("  join drgs.t_adrg adrg on drg.adrg_code = adrg.adrg_code and drg.ver_code = adrg.ver_code \n");
		sql.append("  join drgs.t_mdcgroup mdc on mdc.mdc_code = adrg.mdc_code and mdc.ver_code = drg.ver_code \n");
		sql.append("  order by casecntrow \n");
		return jdbcTemplate.queryForList(sql.toString());
	}

	/**
	 * 
	 *  @Description：添加疑难病组
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.DifficultGroupMonitorApplication#addDiffiGroup(java.lang.String)
	 */
	@Override
	public void addDiffiGroup(String drgCode,String account,String hospCode) {
		String cfgCode = getCfgCode(account,hospCode);
		String sql="  select pd.del_type from drgs.mr_puzzle_drg pd where pd.drg_code='"+drgCode+"' ";
		List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
		String sqlN = "";
		//如果要添加的病组在表中保存为删除标志病组，则更改删除标志即可，否则插入
		if (!list.isEmpty() && list.get(0).get("DEL_TYPE").equals("1")) {
			sqlN += "  update drgs.mr_puzzle_drg pd set pd.del_type='0' where pd.drg_code='"+drgCode+"' and par_moudle_code='"+cfgCode+"'";
			jdbcTemplate.update(sqlN);
		}else{
			String id = UUID.randomUUID().toString();
			sqlN += "  insert into drgs.mr_puzzle_drg (id,par_moudle_code,drg_code,default_type,del_type)  \n";
			sqlN+="  values('"+id+"','"+cfgCode+"','"+drgCode+"','2','0')  ";
			jdbcTemplate.update(sqlN);
		}
		
	}

	/**
	 *  setDiffiGroup.  
	 *  @Description：设置疑难病组
	 *  @Author ZouYiyu
	 *  @param stdId
	 *  @param yearCode
	 *  @param caseCntPer
	 *  @param mdcWeightPer
	 *  @return 
	 *  @Date:2017年12月7日下午3:19:43
	 */
	@Override
	public int setDiffiGroup(String stdId, String yearCode, int caseCntPer, int mdcWeightPer,String groupScheme,String account,String hospCode) {
		int keyDisGroupSize = 0;//重点病组数量
		List<String> caseCntList = new  ArrayList<String>();//病例数 病组   
		List<String> mdcWeightList = new  ArrayList<String>();//mdc权重 病组
		List<String> years=new ArrayList<String>();
		if (yearCode.indexOf(",") > 0) {
		    years = Arrays.asList(yearCode.split(","));
        }else {
        	years.add(yearCode);
        }
		
		StringBuilder sql = new StringBuilder();
		String cfgCode =getCfgCode(account,hospCode);
		String roleCode=DrgsIndex.getRoleCode();
		//存储该用户筛选疑难病组的条件
		String cgfValue = org.apache.commons.lang.StringUtils.join(years, ",")+ "θ" + caseCntPer + "," + mdcWeightPer;//占比
		sql.append(" declare cnt INTEGER; \n ");
		sql.append(" begin  \n ");
		sql.append(" select count(t.moudle_code) into cnt from drgs.MR_SIZER_CONDITION t where t.moudle_code='"+cfgCode+"' and t.user_code='"+account+"';   \n ");
		sql.append(" if cnt = 0 then  \n ");
		sql.append(" insert into drgs.MR_SIZER_CONDITION (moudle_code,codition_value,role_code,user_code) values ('"+cfgCode+"','"+cgfValue+"','"+roleCode+"','"+account+"');  \n ");
		sql.append(" else \n ");
		sql.append(" update drgs.MR_SIZER_CONDITION c set c.codition_value = '"+cgfValue+"' where c.moudle_code = '"+cfgCode+"' and c.role_code='"+roleCode+"' and c.user_code='"+account+"' ;  \n ");
		sql.append(" end if; \n ");
		sql.append(" commit; \n ");
		sql.append(" end; \n ");
		getRepository().createSqlQuery(sql.toString()).executeUpdate();
		//计算各病组的病例数
		sql.setLength(0);
		sql.append(" select pardrg_code from( \n ");
		sql.append(" select pardrg_code,(sum(caseCnt) over(order by caseCnt desc))/(sum(caseCnt) over()) perval from  \n ");
		sql.append(" (select pd.pardrg_code,count(mr.pmainindex) caseCnt from drgs.t_patientdrg pd   \n ");
		sql.append(" left join drgs.mr_ipinfo mr on pd.parid = mr.pmainindex and pd.ver_region='"+groupScheme+"' \n ");
		sql.append(" left join  drgs.mr_baseinfo bs on bs.pmainindex=mr.pmainindex \n");
		sql.append(" where pd.pardrg_code is not null and instr('"+hospCode+"',bs.p00010001)>0 and ( 1<>1  \n ");
		if(null!=years){
			for(int i = 0;i <years.size();i++){
				sql.append(" or to_char(mr.P00290000,'yyyy')='"+years.get(i)+"' \n ");
			}
		}
		sql.append(" ) \n ");
		sql.append(" group by pardrg_code ))where perval <= "+(caseCntPer*1.0)/100+" \n ");
		caseCntList = getQueryChannelService().createSqlQuery(sql.toString()).list();
		sql.setLength(0);
		//计算各 MDC 下各病组权重 排名
		sql.append(" select pardrg_code from ( \n ");
		sql.append(" select mdc,pardrg_code,(sum(totalWeight) over(order by totalWeight desc))/(sum(totalWeight) over()) perval  from (    \n ");
		sql.append(" select pd.mdc,pd.pardrg_code,sum(std.as_weight) totalWeight from drgs.t_patientdrg pd   \n ");
		sql.append(" left join drgs.mr_ipinfo mr on pd.parid = mr.pmainindex and pd.ver_region='"+groupScheme+"' \n ");
		sql.append(" left join  drgs.mr_baseinfo bs on bs.pmainindex=mr.pmainindex  \n");
		sql.append(" left join drgs.t_annualstd std on pd.pardrg_code = std.as_drg_code and std.as_id = '"+stdId+"'   \n ");
		sql.append(" where pd.pardrg_code is not null and instr('"+hospCode+"',bs.p00010001)>0 and std.as_weight is not null and(1<>1  \n ");
		if(null!=years){
			for(int i = 0;i <years.size();i++){
				sql.append(" or to_char(mr.P00290000,'yyyy')='"+years.get(i)+"' \n ");
			}
		}
		sql.append(" )  \n ");
		sql.append(" group by mdc,pardrg_code )) where perval <= "+(mdcWeightPer*1.0)/100+" \n ");
		mdcWeightList = getQueryChannelService().createSqlQuery(sql.toString()).list();
		caseCntList.retainAll(mdcWeightList);//求病例数和MDC权重的交集<<<<<<========疑难病组
		if(caseCntList!=null){
//			//保留默认的疑难病组放表格里，标志位置为1，让其不显示
			String updSql="  update drgs.mr_puzzle_drg pu set pu.del_type='1' where  pu.del_type<>'1' and par_moudle_code='"+cfgCode+"'";
			getRepository().createSqlQuery(updSql).executeUpdate();
			keyDisGroupSize = caseCntList.size();
			//保存新的疑难病组数据
			for(int i = 0;i<caseCntList.size();i++){
				addDiffiGroup(caseCntList.get(i),account,hospCode);
			}
            String delSql = "delete drgs.mr_puzzle_drg pu where pu.del_type='1' and par_moudle_code='"+cfgCode+"'";
			getRepository().createSqlQuery(delSql).executeUpdate();
			
		}
		return keyDisGroupSize;
	}
	/**
	 * 
	 *  getCfgCode.  
	 *  @Description：拼接CfgCode
	 *  @Author WENYI
	 *  @param account
	 *  @param roleName
	 *  @return 
	 *  @Date:2018年1月23日下午1:24:44
	 */
	private String getCfgCode(String account,String hospCode){
		String cfgCode = "DiffiConfig"+"-"+account+"-"+hospCode;
		return cfgCode;
	}
}
